﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
 

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Reflection;


namespace Star.Tools.Excel
{
    public class ExportDataTable
    {
        #region 创建工作薄
        /// <summary>
        /// 创建工作薄  true 2007 flase 2003
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <returns></returns>
        public static IWorkbook CreateWorkbook(bool isCompatible)
        {
            if (isCompatible)
            {
                return new XSSFWorkbook();
            }
            else
            {
                return new HSSFWorkbook();
            }
        }
        #endregion


        #region 数据保存到指定路径
        /// <summary>
        /// 将DataTable数据保存到指定路径
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="strFileName">文件路径包括名字</param>
        public static void Export(DataTable dt, string strFileName)
        {

            using (MemoryStream ms = DataTableToExcel(dt))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }




        /// <summary>
        ///  将DataTable数据保存到指定路径
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="colAliasNames">列名</param>
        /// <param name="sheetName">sheet名</param>
        /// <param name="strFileName">文件路径</param>
        public static void DataTableToExcel(DataTable dt, string sheetName, string[] colAliasNames, string strFileName, bool isCompatible = true, IDictionary<string, string> colDataFormats = null)
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = DataTableToWorkbook(dt, sheetName, colAliasNames, isCompatible, colDataFormats);
            workbook.Write(ms);
            using (FileStream fs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }

        /// <summary>
        /// 将DataSet数据保存到指定路径
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="strFileName">文件路径包括名字</param>
        public static void DataSetToExcel(DataSet dsSource, string strFileName, bool isCompatible = true)
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = DataSetToWorkbook(dsSource, isCompatible);
            workbook.Write(ms);
            using (FileStream fs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }

        }
        #endregion

        #region  数据保存MemoryStream
        /// <summary>
        /// 将DataTable数据保存MemoryStream
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="strFileName">文件路径包括名字</param>
        public static MemoryStream DataTableToMemoryStream(DataTable dt, string[] colAliasNames, string sheetName, string strFileName)
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = DataTableToWorkbook(dt, sheetName, colAliasNames);
            workbook.Write(ms);
            return ms;
        }

        /// <summary>
        /// 将DataSet数据保存到MemoryStream
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="strFileName">文件路径包括名字</param>
        public static MemoryStream DataSetToMemoryStream(DataSet dsSource, string strFileName, bool isCompatible = true)
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = DataSetToWorkbook(dsSource, isCompatible);
            workbook.Write(ms);
            return ms;

        }
        #endregion



        #region  单sheet导出DataTable到MemoryStream
        /// <summary>
        /// 导出DataTable到MemoryStream
        /// </summary>
        /// <param name="dt">要导出数据的DataTable,DataTable的TableName为sheet页名</param>
        /// <param name="isOOXML">office2007及以上请选true，office2003请选false</param>
        ///<param name="strHeaderText">标题头</param>
        /// <param name="filePath"></param>
        public static MemoryStream DataTableToExcel(DataTable dt, bool isOOXML = true, string strHeaderText = null)
        {
            if (dt == null) { return null; }
            IWorkbook workbook = CreateWorkbook(isOOXML);//创建工作簿

            if (string.IsNullOrEmpty(dt.TableName))
            {
                dt.TableName = "Sheet1";
            }
            ISheet sheet = workbook.CreateSheet(dt.TableName);

            IRow row = null;
            int indexRow = 0;

            #region 标题头
            if (!string.IsNullOrEmpty(strHeaderText))
            {
                row = sheet.CreateRow(indexRow);
               // CellExtend.AddMergedRegion(sheet, indexRow, 1, 0, dt.Columns.Count);
                indexRow += 1;
            }
            #endregion

            if (dt.Rows.Count > 0)
            {
                row = sheet.CreateRow(indexRow);
                indexRow++;
                //冻结标题行
                // sheet.CreateFreezePane(0, 1, 0, 1);
                #region 列头
                #region 取得列宽
                //int[] arrColWidth = new int[dt.Columns.Count];
                //foreach (DataColumn item in dt.Columns)
                //{
                //    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                //}
                #endregion
                foreach (DataColumn column in dt.Columns)
                {
                    var i = column.Ordinal;
                    string columnName = column.ColumnName;
                    if (string.IsNullOrEmpty(columnName))
                    {
                        columnName = "column" + (i + 1).ToString();
                    }
                    row.CreateCell(i).SetCellValue(column.ColumnName);
                    //row.GetCell(i).CellStyle = headStyle;
                    //设置列宽
                    // sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                }

                #endregion

                #region 填充数据

                foreach (DataRow drRow in dt.Rows)
                {
                    if (indexRow == 65533)
                    {
                        break;
                    }
                    row = sheet.CreateRow(indexRow);
                    foreach (DataColumn column in dt.Columns)
                    {
                        // row.CreateCell(column.Ordinal).SetCellValue(drRow[column].ToString());
                        ICell newCell = row.CreateCell(column.Ordinal);
                        string dataType = drRow[column].GetType().ToString();
                        string drValue = drRow[column].ToString();
                        SetCellValues(workbook, newCell, dataType, drValue);
                    }
                    indexRow++;
                }
                #endregion

                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    // ms.Position = 0;
                    //sheet.Dispose();
                    //workbook.Dispose();
                    return ms;
                }
                //  return null;
            }



            return null;
        }
        #endregion

        #region  单sheet导出DataTable到IWorkbook


        /// <summary>
        /// 导出DataTable到IWorkbook
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>  
        /// <param name="sheetName">sheet页名字</param>
        /// <param name="colAliasNames">列名数组与DataTable的列顺序一致</param>
        /// <param name="isCompatible">是否兼容true 2007 false 2003</param>
        /// <param name="colDataFormats"></param>
        /// <returns></returns>
        public static IWorkbook DataTableToWorkbook(DataTable dtSource, string sheetName, string[] colAliasNames,
            // IDictionary<string, string> colAliasNames = null,
            bool isCompatible = true, IDictionary<string, string> colDataFormats = null) //
        {
            if (dtSource.Rows.Count <= 0) return null;

            if (colAliasNames == null || dtSource.Columns.Count != colAliasNames.Length)
            {
                throw new ArgumentException("列名重命名数组与DataTable列集合不匹配。", "colAliasNames");
            }

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle headerCellStyle = GetCellStyle(workbook, true);

            Dictionary<int, ICellStyle> colStyles = new Dictionary<int, ICellStyle>();
            ISheet sheet;
            if (string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.CreateSheet();
            }
            else
            {
                sheet = workbook.CreateSheet(sheetName);
            }
            int rowIndex = 0;
            IRow headerRow = sheet.CreateRow(rowIndex);
            rowIndex++;
            #region 列头
            foreach (DataColumn column in dtSource.Columns)
            {
                ICell headerCell = headerRow.CreateCell(column.Ordinal);
                //if (colAliasNames != null && colAliasNames.ContainsKey(column.ColumnName))
                //{
                //    headerCell.SetCellValue(colAliasNames[column.ColumnName]);
                //}
                //else
                //{
                //    headerCell.SetCellValue(colAliasNames[column.ColumnName]);
                //}
                headerCell.SetCellValue(colAliasNames[column.Ordinal]);
                headerCell.CellStyle = headerCellStyle;
                sheet.AutoSizeColumn(headerCell.ColumnIndex);
                if (colDataFormats != null && colDataFormats.ContainsKey(column.ColumnName))
                {
                    colStyles[headerCell.ColumnIndex] = GetCellStyleWithDataFormat(workbook, colDataFormats[column.ColumnName]);
                }
                else
                {
                    colStyles[headerCell.ColumnIndex] = GetCellStyle(workbook);
                }

            }
            #endregion

            #region 填充数据

            foreach (DataRow drRow in dtSource.Rows)
            {

                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in dtSource.Columns)
                {
                    ICell cell = dataRow.CreateCell(column.Ordinal);
                    SetCellValue(cell, (drRow[column] ?? "").ToString(), column.DataType, colStyles);
                  //  ReSizeColumnWidth(sheet, cell);
                }

                rowIndex++;
            }
            #endregion
            sheet.ForceFormulaRecalculation = true;
            return workbook;
        }




        #endregion

        #region 导出DataSet到IWorkbook
        /// <summary>
        /// 导出DataSet到IWorkbook
        /// </summary>
        /// <param name="sourceDs">要导出数据的DataSet</param>
        /// <param name="filePath">导出路径，可选</param>
        /// <returns></returns>
        public static IWorkbook DataSetToWorkbook(DataSet dsSource, bool isCompatible = true)
        {

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle headerCellStyle = GetCellStyle(workbook, true);

            for (int i = 0; i < dsSource.Tables.Count; i++)
            {
                DataTable table = dsSource.Tables[i];
                string sheetName = string.IsNullOrEmpty(table.TableName) ? "result" + i.ToString() : table.TableName;
                ISheet sheet = workbook.CreateSheet(sheetName);
                IRow headerRow = sheet.CreateRow(0);
                Dictionary<int, ICellStyle> colStyles = new Dictionary<int, ICellStyle>();

                foreach (DataColumn column in table.Columns)
                {
                    ICell headerCell = headerRow.CreateCell(column.Ordinal);
                    headerCell.SetCellValue(column.ColumnName);
                    headerCell.CellStyle = headerCellStyle;
                    sheet.AutoSizeColumn(headerCell.ColumnIndex);
                    colStyles[headerCell.ColumnIndex] = GetCellStyle(workbook);
                }


                int rowIndex = 1;

                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in table.Columns)
                    {
                        ICell cell = dataRow.CreateCell(column.Ordinal);

                        SetCellValue(cell, (row[column] ?? "").ToString(), column.DataType, colStyles);
                      //  ReSizeColumnWidth(sheet, cell);
                    }

                    rowIndex++;
                }
                sheet.ForceFormulaRecalculation = true;
            }
            return workbook;

        }

        #endregion

       // #region 单元格操作
        /// <summary>
        /// 创建单元格样式
        /// </summary>
        /// <param name="workbook">workbook</param>
        /// <param name="isHeaderRow">是否获取头部样式</param>
        /// <returns></returns>
        public static ICellStyle GetCellStyle(IWorkbook workbook, bool isHeaderRow = false)
        {
            ICellStyle style = workbook.CreateCellStyle();

            if (isHeaderRow)
            {
                style.FillPattern = FillPattern.SolidForeground;
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BrightGreen.Index;
                IFont f = workbook.CreateFont();
                f.Boldweight = (short)FontBoldWeight.Bold;
                style.SetFont(f);
            }

            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            return style;
        }

        /// <summary>
        /// 创建单元格样式并设置数据格式化规则 ok
        /// </summary>
        /// <param name="workbook">workbook</param>
        /// <param name="format">格式化字符串</param>
        public static ICellStyle GetCellStyleWithDataFormat(IWorkbook workbook, string format)
        {
            var style = GetCellStyle(workbook);
            var dataFormat = workbook.CreateDataFormat();
            short formatId = -1;
            if (dataFormat is HSSFDataFormat)
            {
                formatId = HSSFDataFormat.GetBuiltinFormat(format);
            }
            if (formatId != -1)
            {
                style.DataFormat = formatId;
            }
            else
            {
                style.DataFormat = dataFormat.GetFormat(format);
            }
            return style;
        }

        /// <summary>
        /// 依据值类型为单元格设置值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="value"></param>
        /// <param name="colType"></param>
        /// <param name="colStyles"></param>
        public static void SetCellValue(ICell cell, string value, Type colType, IDictionary<int, ICellStyle> colStyles)
        {
            string dataFormatStr = null;
            switch (colType.ToString())
            {
                case "System.String": //字符串类型
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(value);
                    break;
                case "System.DateTime": //日期类型
                    DateTime dateV;
                    if (DateTime.TryParse(value, out dateV))
                    {
                        cell.SetCellValue(dateV);
                    }
                    dataFormatStr = "yyyy/mm/dd hh:mm:ss";
                    break;
                case "System.Boolean": //布尔型
                    bool boolV = false;
                    if (bool.TryParse(value, out boolV))
                    {
                        cell.SetCellType(CellType.Boolean);
                        cell.SetCellValue(boolV);
                    }
                    break;
                case "System.Int16": //整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    if (int.TryParse(value, out intV))
                    {
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue(intV);
                    }
                    dataFormatStr = "0";
                    break;
                case "System.Decimal": //浮点型
                case "System.Double":
                    double doubV = 0;
                    if (double.TryParse(value, out doubV))
                    {
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue(doubV);
                    }
                    dataFormatStr = "0.00";
                    break;
                case "System.DBNull": //空值处理
                    cell.SetCellType(CellType.Blank);
                    cell.SetCellValue("");
                    break;
                default:
                    cell.SetCellType(CellType.Unknown);
                    cell.SetCellValue(value);
                    break;
            }

            if (!string.IsNullOrEmpty(dataFormatStr) && colStyles[cell.ColumnIndex].DataFormat <= 0) //没有设置，则采用默认类型格式
            {
                colStyles[cell.ColumnIndex] = GetCellStyleWithDataFormat(cell.Sheet.Workbook, dataFormatStr);
            }
            cell.CellStyle = colStyles[cell.ColumnIndex];
        }
        #region 设置单元格数据类型
        /// <summary>
        /// 设置单元格数据类型
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="cellType">数据类型</param>
        /// <param name="cellValue">值</param>
        public static void SetCellValues(IWorkbook workbook, ICell cell, string cellType, string cellValue)
        {
            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy/MM/dd");
            switch (cellType)
            {
                case "System.String": //字符串类型
                    double result;
                    cell.SetCellType(CellType.String);
                    if (double.TryParse(cellValue, out result))
                        cell.SetCellValue(result);
                    else
                        cell.SetCellValue(cellValue);

                    break;
                case "System.DateTime": //日期类型
                    DateTime dateV;
                    DateTime.TryParse(cellValue, out dateV);
                    cell.CellStyle = dateStyle;
                    cell.SetCellValue(dateV);

                    break;
                case "System.Boolean": //布尔型
                    bool boolV;
                    if (bool.TryParse(cellValue, out boolV))
                    {
                        cell.SetCellType(CellType.Boolean);
                        cell.SetCellValue(boolV);
                    }
                    break;
                case "System.Int16": //整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV;
                    if (int.TryParse(cellValue, out intV))
                    {
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue(intV);
                    }
                    break;
                case "System.Decimal": //浮点型
                case "System.Double":
                    double doubV;
                    if (double.TryParse(cellValue, out doubV))
                    {
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue(doubV);
                    }
                    break;
                case "System.DBNull": //空值处理
                    cell.SetCellType(CellType.Blank);
                    cell.SetCellValue("");
                    break;
                default:
                    cell.SetCellType(CellType.Unknown);
                    cell.SetCellValue("");
                    break;
            }
        }
        #endregion
    }
}
